import subprocess
import re
import pandas as pd
from read_xlsx_sheet1 import read_execle,Coating_Name,Coating_Name_row_element


def replace_text_in_file_sheet2_Define(file_par, file_xlsx,start_tag, end_tag,a):
    sheet_name = f'Sheet{a}'  # 如果需要，指定工作表名称
    df = pd.read_excel(file_xlsx, sheet_name=sheet_name,engine='openpyxl')
    #打印表格内容
    two_row=0
    search_value = 'Emitter'
    found = False
    for index, row in df.iterrows():
        for col_index, cell_value in enumerate(row):
            # 检查单元格是否为字符串并且是否包含搜索值
            if isinstance(cell_value, str) and search_value == cell_value:
                # print(f"找到 '{search_value}' 在行索引 {index}，列索引 {col_index}")
                two_row=index
                found = True
                break  # 找到后可以退出内层循环
        if found:
            break
    if not found:
        print(f"在DataFrame中没有找到 '{search_value}'")



    list1=[]
    # 遍历第0行到第12行
    for row in df.iloc[0:two_row].values:
        # 使用列表推导式来确保每个元素都被转换为字符串，同时跳过NaN值
        cleaned_row = ['{}'.format(item) if not pd.isna(item) else '' for item in row]
        # print(' '.join(cleaned_row).strip())
        list1.append(' '.join(cleaned_row).strip())



    with open(file_par, 'r') as file:
        lines = file.readlines()

    start_extract = False
    modified_lines = []
    #模糊查询end_tag
    end_tag_pattern = re.compile(end_tag)

    for line in lines:
        if start_tag in line:  # 如果找到 start_tag，包括这一行
            modified_lines.append(line)
            start_extract = True
            # 添加新内容，每个元素占据一行
            for content in list1:
                modified_lines.append(content + '\n')
            continue
        elif start_extract:
            if end_tag_pattern.search(line):  # 如果找到 end_tag，跳过这一行
                modified_lines.append(line)
                start_extract = False
                continue  # 跳过 end_tag 所在的行
            else:
                # 跳过旧内容，因为我们已经用新内容替换了
                continue
        else:
            modified_lines.append(line)  # 复制旧内容

    with open(file_par, 'w') as file:
        file.writelines(modified_lines)


def replace_text_in_file_sheet2_Emitter(file_par, file_xlsx,start_tag, end_tag,a,pl_path):
    sheet_name = f'Sheet{a}'  # 如果需要，指定工作表名称
    df = pd.read_excel(file_xlsx, sheet_name=sheet_name,engine='openpyxl')
    #存放Emitter下表
    list_index=[]

    #存放结果
    list_result=[]
    search_value = 'Emitter'
    # found = False

    for index, row in df.iterrows():
        for col_index, cell_value in enumerate(row):
            # 检查单元格是否为字符串并且是否包含搜索值
            if isinstance(cell_value, str) and search_value == cell_value:
                # print(f"找到 '{search_value}' 在行索引 {index}，列索引 {col_index}")
                list_index.append(index)
                # print(list_index)
                # found = True
                break  # 找到后可以退出内层循环
    for row in df.iloc[list_index[0]:].values:
        # 使用列表推导式来确保每个元素都被转换为字符串，同时跳过NaN值
        cleaned_row = ['{}'.format(item) if not pd.isna(item) else '' for item in row]
        combined_row = ' '.join(cleaned_row).strip()
        
        # 模糊匹配和替换
        if '发光层' in combined_row:
            combined_row = combined_row.replace('发光层', 'Layer')
            
        if '发光光谱' in combined_row:
            combined_row = combined_row.replace('发光光谱', 'LuminescenceSpectrum')
       
        if 'LuminescenceSpectrum' in combined_row:
                parts = combined_row.split('LuminescenceSpectrum')[-1].strip()
                file_names = parts.split()

                # for file_name in file_names:
                new_string = f'File "{pl_path}{file_names[0]}.txt" Power'
                list_result.append('LuminescenceSpectrum')
                list_result.append(f'{new_string}')
        else:
                # print(combined_row)
                list_result.append(combined_row)


    
    with open(file_par, 'r') as file:
        lines = file.readlines()

    start_extract = False
    modified_lines = []
    #模糊查询end_tag
    end_tag_pattern = re.compile(end_tag)

    for line in lines:
        if start_tag in line:  # 如果找到 start_tag，包括这一行
            # modified_lines.append(line)
            start_extract = True
            # 添加新内容，每个元素占据一行
            for content in list_result:
                modified_lines.append(content + '\n')
            continue
        elif start_extract:
            if end_tag_pattern.search(line):  # 如果找到 end_tag，跳过这一行
                modified_lines.append(line)
                start_extract = False
                continue  # 跳过 end_tag 所在的行
            else:
                # 跳过旧内容，因为我们已经用新内容替换了
                continue
        else:
            modified_lines.append(line)  # 复制旧内容

    with open(file_par, 'w') as file:
        file.writelines(modified_lines)
    # print(list_result)
        # list1.append(' '.join(cleaned_row).strip())
    # print(list_index) 12  19  
# file_par = 'emitter.txt'  # 替换为你的文件路径
# file_xlsx ='C:\\Users\\admin\\Desktop\\主动光学仿真\\仿真设置.xlsx'
# start_tag = 'Emitter'
# end_tag = 'Commands'

# replace_text_in_file_sheet2_Emitter(file_par, file_xlsx,start_tag, end_tag,2,'C:/Users/admin/Desktop/re_name_setfos/NK/')


